const { Service } = require('egg');
const product_table = 'product',
  barcode_table = 'barcode',
  inventory_table = 'inventory',
  user_table = 'user';

class InventoryService extends Service {
  async list(name, barcode, type, order, method, pageIndex, pageSize) {
    const ctx = this.ctx;
    try {
      const sql = ctx.app.knex().select('t1.uuid', 't2.name as product_name', 't3.content as barcode',
        't1.number', 't1.type', 't1.create_time', 't2.update_time', 't4.name as create_name', 't5.name as update_name'
      )
        .from(`${inventory_table} as t1`)
        .leftJoin(`${product_table} as t2`, function() {
          this.on('t1.product_id', '=', 't2.uuid');
        })
        .leftJoin(`${barcode_table} as t3`, function() {
          this.on('t1.barcode_id', '=', 't3.uuid');
        })
        .leftJoin(`${user_table} as t4`, function() {
          this.on('t1.create_by', '=', 't4.uuid');
        })
        .leftJoin(`${user_table} as t5`, function() {
          this.on('t1.update_by', '=', 't5.uuid');
        })
        .where('t1.is_delete', 0);

      const countSql = ctx.app.knex().count('t1.uuid as count')
        .from(`${inventory_table} as t1`)
        .leftJoin(`${product_table} as t2`, function() {
          this.on('t1.product_id', '=', 't2.uuid');
        })
        .leftJoin(`${barcode_table} as t3`, function() {
          this.on('t1.barcode_id', '=', 't3.uuid');
        })
        .where('t1.is_delete', 0);

      if (name) {
        sql.andWhereLike('t2.name', `%${name}%`);
        countSql.andWhereLike('t2.name', `%${name}%`);
      }

      if (barcode) {
        sql.andWhereLike('t3.content', `%${barcode}%`);
        countSql.andWhereLike('t3.content', `%${barcode}%`);
      }

      if (type) {
        sql.andWhereLike('t1.type', `%${type}_`);
        countSql.andWhereLike('t1.type', `%${type}_`);
      }

      sql.orderBy(order, method).limit(Number(pageIndex)).offset((Number(pageIndex) - 1) * Number(pageSize));

      const list = await sql;
      const count = await countSql.first();

      const result = {
        list,
        count: count.count,
      };

      return result;
    } catch (error) {
      throw error;
    }
  }
}

module.exports = InventoryService;
